"""
@Author: CheyenneLam
@Date: 2020-08-01 09:00:00
@LastEditTime: 2020-08-01 09:00:00
@LastEditors: CheyenneLam
@Description: 生成更新模型中，对应数据源表的全量、增量、视图表及视图表逻辑
@FilePath: \create_update_mode_table.py
"""


from odps import ODPS
from odps.df import DataFrame

# 读取system_table_info的表，获取full表名、ddl建表语句
dataworksDf = DataFrame(o.get_table('system_table_info')).to_pandas()

"""***************************************************（注意，必改）每次跑之前都确认一下需要新建的表或是全量新建******************************************************"""
# 每次跑表之前都设置好需要生成的表
# 需要新建的表
tableList = [
    'attribution_record_log'
]
# 若需要全部表重建，则把下面的去除注释
# 全部表
# tableList = dataworksDf['data_source_table'].values


"""*********************************************************用于建log表的配置，数据类型转换***********************************************************"""
typeDict = {
    'INT64': 'BIGINT',
    'int64': 'BIGINT',
    'float64': 'DOUBLE',
    'list<int64>': 'ARRAY<BIGINT>',
    'list<float64>': 'ARRAY<DOUBLE>',
}

logAddColumn = {
    'before_image': 'STRING',
    'sequence_id': 'BIGINT',
    'after_image': 'STRING',
    'operation_type': 'STRING',
}

"""***************************************************这部分是方法，用于建full表、inc增量表、log表、实时视图view******************************************************"""


# 用于新建full表、inc增量表
def createFullIncTable(dwFullTableName, ddlSql):
    """
    @description:用于新建full表、inc增量表
    @param dwFullTableName:需要新建的full表表名
    @param ddlSqlStr:新建full表DDL语句
    """
    fullDDLSqlList = ddlSql.split(';')

    # 建全量表
    print('删除全量表语句', fullDDLSqlList[0])
    o.execute_sql(fullDDLSqlList[0])
    print('建全量表语句', fullDDLSqlList[1])
    o.execute_sql(fullDDLSqlList[1])

    # 建增量表
    incTableName = 'inc_' + dwFullTableName[5:]
    incDDLSqlList = [x.replace(dwFullTableName, incTableName) for x in fullDDLSqlList]
    print('删除增量表语句', incDDLSqlList[0])
    o.execute_sql(incDDLSqlList[0])
    print('建增量表语句', incDDLSqlList[1])
    o.execute_sql(incDDLSqlList[1])

    # 完成
    print("新建完成全量表：", dwFullTableName, ", 新建完成增量表：", incTableName)


# 用于新建log表
def createLogTable(dwFullTableName):
    """
    @description:用于新建log表
    @param dwFullTableName:需要新建log表的full表表名
    """

    dropSql = f"DROP TABLE IF EXISTS {dwFullTableName}_binlog;"
    o.execute_sql(dropSql)
    print("删除了原log表语句", dropSql)

    fullDf = o.get_table(dwFullTableName).to_df()
    # 根据full表创建binlog表
    insertSql = ',\n'.join(
        [x.name + ' ' + typeDict.get(x.type.name.lower(), x.type.name) for x in fullDf.schema] + [' '.join(x) for x in
                                                                                                  logAddColumn.items()])
    strsql = f"""
    CREATE TABLE IF NOT EXISTS {dwFullTableName}_binlog
    ({insertSql})
    PARTITIONED BY
    (
    mdf_year           STRING COMMENT 'modify year',
    mdf_month          STRING COMMENT 'modify month',
    mdf_day            STRING COMMENT 'modify day'
    )
    lifecycle 7
    ;
    """
    print(strsql)
    o.execute_sql(strsql)
    print(f"新建完成{dwFullTableName}_binlog表")


# 用于新建实时视图
def createView(dwFullTableName, primaryKey):
    """
    @description:用于新建实时视图
    @param dwFullTableName:需要新建实时视图的full表表名
    @param primaryKey:full表主键
    """

    fullDf = o.get_table(dwFullTableName).to_df()
    # 删视图
    o.execute_sql(f"""DROP VIEW IF EXISTS view_{dwFullTableName};""")

    # 按照full表结构建立merge语句，且按照full表的主键建立条件语句
    insertSql = ',\n'.join(
        [
            f"""CASE WHEN {' and '.join(['inc.{} is not null'.format(y) for y in primaryKey])} THEN inc.{x.name} ELSE t.{x.name} end as {x.name}"""
            for x in fullDf.schema])
    strsql = f"""
    CREATE VIEW IF NOT EXISTS  view_{dwFullTableName} AS 
    SELECT {insertSql}
    FROM {dwFullTableName} AS t
    FULL OUTER JOIN (
         SELECT  *
                    FROM    (
                                SELECT  ROW_NUMBER() OVER (PARTITION BY {', '.join(['t.{}'.format(y) for y in primaryKey])} ORDER BY t.sequence_id DESC,t.after_image DESC) AS nums
                                        ,t.*
                                FROM    (
                                            SELECT * 
                                            FROM    {dwFullTableName}_binlog
                                            WHERE 
                                            (
                                                mdf_year = YEAR(GETDATE())
                                            and   mdf_month = MONTH(GETDATE())
                                            and  mdf_day = DAY(GETDATE())

                                            )  or (
                                                mdf_year = YEAR(DATEADD(GETDATE(),-1,'dd'))    
                                            and   mdf_month =  MONTH(DATEADD(GETDATE(),-1,'dd')) 
                                            and  mdf_day =  DAY(DATEADD(GETDATE(),-1,'dd'))
                                            )
                                        ) t
                            ) t1
                    WHERE   t1.nums = 1
                ) AS inc
                ON {' and '.join(['inc.{} = t.{}'.format(y, y) for y in primaryKey])}
                WHERE {' and '.join(['inc.{} is null'.format(y) for y in primaryKey])}
                OR ({' and '.join(['inc.{} is not null'.format(y) for y in primaryKey])} AND inc.operation_type in("UPDATE","INSERT") );"""
    print(strsql)
    o.execute_sql(strsql)
    print(f"新建完成{dwFullTableName}实时视图")


"""*********************************************************开始调用前面的方法，建四种表***********************************************************"""

# 循环，每个表分别建立full、inc表，log表，实时视图
for tableName in tableList:
    # 获取full表表名、创建full表的ddl、full表主键
    tableInfo = dataworksDf.query("data_source_table == @tableName")
    dwFullTableName = tableInfo['dw_full_table_name'].values[0]
    ddlSql = tableInfo['ddl_sql'].values[0]
    primaryKey = tableInfo['pk_name'].values[0]

    # 建full表和inc表
    createFullIncTable(dwFullTableName, ddlSql)
    # 建log表
    createLogTable(dwFullTableName)
    # 建立实时view
    createView(dwFullTableName, primaryKey)
    print(f"完成新建{tableName}的全量表、增量表、日志表、视图")